import express from 'express';
import { dbPool, redisClient } from '../config/database.js';

const router = express.Router();

// 获取仪表盘统计数据
router.get('/stats', async (req, res) => {
  try {
    // 尝试从Redis缓存获取数据
    const cacheKey = 'dashboard:stats';
    const cachedData = await redisClient.get(cacheKey);
    
    if (cachedData) {
      return res.json({
        success: true,
        data: JSON.parse(cachedData),
        cached: true
      });
    }
    
    // 并行查询所有统计数据
    const [
      totalProducts,
      totalOrders,
      totalArticles,
      totalUsers,
      todayOrders,
      todayRevenue,
      conversionRate,
      abandonedRate
    ] = await Promise.all([
      // 总产品数
      dbPool.query('SELECT COUNT(*) FROM products WHERE status = $1', ['active']),
      // 总订单数
      dbPool.query('SELECT COUNT(*) FROM orders'),
      // 总文章数
      dbPool.query('SELECT COUNT(*) FROM articles WHERE status = $1', ['published']),
      // 总用户数
      dbPool.query('SELECT COUNT(*) FROM users'),
      // 今日订单数
      dbPool.query(`SELECT COUNT(*) FROM orders 
                   WHERE DATE(created_at) = CURRENT_DATE`),
      // 今日收入
      dbPool.query(`SELECT COALESCE(SUM(total_amount), 0) as revenue 
                   FROM orders WHERE DATE(created_at) = CURRENT_DATE`),
      // 转化率（简化版本）
      dbPool.query(`SELECT 
        ROUND((COUNT(*) * 100.0 / 
        GREATEST((SELECT COUNT(*) FROM users 
        WHERE DATE(created_at) = CURRENT_DATE), 1)), 2) as conversion_rate
        FROM orders WHERE DATE(created_at) = CURRENT_DATE`),
      // 弃购率（简化版本）
      dbPool.query(`SELECT 
        ROUND((COUNT(*) * 100.0 / 
        GREATEST((SELECT COUNT(*) FROM orders 
        WHERE DATE(created_at) = CURRENT_DATE), 1)), 2) as abandoned_rate
        FROM orders WHERE status = 'cancelled' AND DATE(created_at) = CURRENT_DATE`)
    ]);
    
    const stats = {
      totalProducts: parseInt(totalProducts.rows[0].count),
      totalOrders: parseInt(totalOrders.rows[0].count),
      totalArticles: parseInt(totalArticles.rows[0].count),
      totalUsers: parseInt(totalUsers.rows[0].count),
      todayOrders: parseInt(todayOrders.rows[0].count),
      todayRevenue: parseFloat(todayRevenue.rows[0].revenue || 0),
      conversionRate: parseFloat(conversionRate.rows[0].conversion_rate || 0),
      abandonedRate: parseFloat(abandonedRate.rows[0].abandoned_rate || 0)
    };
    
    // 缓存数据5分钟
    await redisClient.setEx(cacheKey, 300, JSON.stringify(stats));
    
    res.json({
      success: true,
      data: stats,
      cached: false
    });
  } catch (error) {
    console.error('获取仪表盘统计数据错误:', error);
    res.status(500).json({
      success: false,
      message: '获取统计数据失败'
    });
  }
});

// 获取销售趋势数据
router.get('/sales-trend', async (req, res) => {
  try {
    const { period = 'week' } = req.query;
    
    let query;
    switch (period) {
      case 'day':
        query = `
          SELECT DATE_TRUNC('hour', created_at) as time_period,
                 COUNT(*) as order_count,
                 COALESCE(SUM(total_amount), 0) as revenue
          FROM orders 
          WHERE created_at >= CURRENT_DATE
          GROUP BY DATE_TRUNC('hour', created_at)
          ORDER BY time_period
        `;
        break;
      case 'week':
        query = `
          SELECT DATE(created_at) as time_period,
                 COUNT(*) as order_count,
                 COALESCE(SUM(total_amount), 0) as revenue
          FROM orders 
          WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
          GROUP BY DATE(created_at)
          ORDER BY time_period
        `;
        break;
      case 'month':
        query = `
          SELECT DATE(created_at) as time_period,
                 COUNT(*) as order_count,
                 COALESCE(SUM(total_amount), 0) as revenue
          FROM orders 
          WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
          GROUP BY DATE(created_at)
          ORDER BY time_period
        `;
        break;
      default:
        return res.status(400).json({
          success: false,
          message: '不支持的统计周期'
        });
    }
    
    const result = await dbPool.query(query);
    
    const trendData = result.rows.map(row => ({
      timePeriod: row.time_period,
      orderCount: parseInt(row.order_count),
      revenue: parseFloat(row.revenue)
    }));
    
    res.json({
      success: true,
      data: trendData,
      period
    });
  } catch (error) {
    console.error('获取销售趋势错误:', error);
    res.status(500).json({
      success: false,
      message: '获取销售趋势失败'
    });
  }
});

// 获取热门产品
router.get('/top-products', async (req, res) => {
  try {
    const { limit = 10 } = req.query;
    
    const result = await dbPool.query(`
      SELECT p.id, p.name, p.price, p.image_url,
             COUNT(oi.id) as sales_count,
             SUM(oi.quantity) as total_quantity
      FROM products p
      LEFT JOIN order_items oi ON p.id = oi.product_id
      WHERE p.status = 'active'
      GROUP BY p.id, p.name, p.price, p.image_url
      ORDER BY sales_count DESC, total_quantity DESC
      LIMIT $1
    `, [limit]);
    
    const topProducts = result.rows.map(row => ({
      id: row.id,
      name: row.name,
      price: parseFloat(row.price),
      imageUrl: row.image_url,
      salesCount: parseInt(row.sales_count),
      totalQuantity: parseInt(row.total_quantity)
    }));
    
    res.json({
      success: true,
      data: topProducts
    });
  } catch (error) {
    console.error('获取热门产品错误:', error);
    res.status(500).json({
      success: false,
      message: '获取热门产品失败'
    });
  }
});

// 获取实时活动
router.get('/recent-activities', async (req, res) => {
  try {
    const { limit = 20 } = req.query;
    
    const result = await dbPool.query(`
      (
        SELECT 'order' as type, id, '新订单创建' as description, created_at as timestamp
        FROM orders 
        ORDER BY created_at DESC 
        LIMIT $1
      )
      UNION ALL
      (
        SELECT 'article' as type, id, '新文章发布' as description, created_at as timestamp
        FROM articles 
        WHERE status = 'published'
        ORDER BY created_at DESC 
        LIMIT $1
      )
      UNION ALL
      (
        SELECT 'user' as type, id, '新用户注册' as description, created_at as timestamp
        FROM users 
        ORDER BY created_at DESC 
        LIMIT $1
      )
      ORDER BY timestamp DESC
      LIMIT $1
    `, [limit]);
    
    const activities = result.rows.map(row => ({
      type: row.type,
      id: row.id,
      description: row.description,
      timestamp: row.timestamp
    }));
    
    res.json({
      success: true,
      data: activities
    });
  } catch (error) {
    console.error('获取实时活动错误:', error);
    res.status(500).json({
      success: false,
      message: '获取实时活动失败'
    });
  }
});

export default router;